package org.andiv.android.esemeser;

import java.util.StringTokenizer;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
/**
 * Helper for manipulating data in database sqlite  
 * @see SQLiteOpenHelper
 * @author andrej
 *
 */
public class Db extends SQLiteOpenHelper {

//  private static final String TAG = "org.andiv.android.esemeser.Db";
  /**
   * Curent database version, if the database is older, {@link #onUpgrade} will be used to upgrade the database
   */
  private static final int DB_VERSION = 3;
  /**
   * Name of database
   */
  private static final String DB_NAME = "esemeser";
  /**
   * SQL to create database
   */
  private static final String DB_CREATE_SQL = 
    "CREATE TABLE msgdata (_id INTEGER PRIMARY KEY, " +
  		                    "disptext VARCHAR(20), " +
  		                    "phone VARCHAR(100), " +
  		                    "bodytext VARCHAR(160), " +
  		                    "srt INTEGER, " +
  		                    "iconpath INTEGER);";
  private static final String[] tables = {"msgdata"};
  /**
   * Array of field names of msgdata table used for creating {@link android.content.ContentValues}
   */
  public static final String[] msgdata_fields = {"disptext","phone","bodytext","srt","iconpath"};
//  private Context ctxt;
  /**
   * Default constructor. Calls super(context, DB_NAME, null, DB_VERSION).
   */
  public Db(Context context) {
    super(context, DB_NAME, null, DB_VERSION);
//    ctxt = context;
  }
  /**
   * Constructor used in {@link #onUpgrade(SQLiteDatabase, int, int) }
   * @param c
   * @param v
   */
  public Db(Context c, int v) {
    super(c, null, null, v);
  }
  @Override
  public void onCreate(SQLiteDatabase db) {
    db.execSQL(DB_CREATE_SQL);
  }

  @Override
  public void onUpgrade(SQLiteDatabase db, int old_v, int new_v) {
    if (new_v <= old_v) return;
    SQLiteDatabase memDB = SQLiteDatabase.create(null);
    memDB.execSQL(DB_CREATE_SQL);
    copyDb(db, memDB);
    for (int i = 0; i < tables.length; i++) {
      db.execSQL("DROP TABLE IF EXISTS "+tables[i]);
    }
    db.execSQL(DB_CREATE_SQL);
    copyDb(memDB, db);
    memDB.close();
  }
  private void copyDb(SQLiteDatabase dbfrom, SQLiteDatabase dbto) {
    for (int i = 0; i < tables.length; i++) {
      Cursor c = dbfrom.query(tables[i], null, null, null, null, null, null);
      if (c.getCount() > 0) for (c.moveToFirst(); c.isAfterLast(); c.moveToNext()) {
        ContentValues values = new ContentValues();
        for (int j = 0; j < c.getColumnCount(); j++) {
          values.put(c.getColumnName(j), c.getString(j));
        }
        dbto.insert(tables[i], null, values);
      }
    }
    
  }
  /**
   * Deletes record of msgdata table with given _id
   * @param id
   */
  public void deleteMsgdata(int id) {
    getWritableDatabase().execSQL("DELETE FROM msgdata where _id = "+id);
  }
  
  /**
   * Deletes all records in msgdata. Parental Advisory :)
   */
  public void deleteAllMsgdata() {
    getWritableDatabase().execSQL("DELETE FROM msgdata");
  }
  
  /**
   * Populates four records with predefined phones, 'plateNo' body text and default icon for Zagreb m-parking
   * @param _db database to populate in
   * @param plateNo
   */
  public void populateZgParkingData(SQLiteDatabase _db, String plateNo) {
    _db.execSQL("INSERT INTO msgdata (disptext, phone, bodytext, srt, iconpath) values ('Park 101','101','" + plateNo + "',1," + R.drawable.p101 + ")");
    _db.execSQL("INSERT INTO msgdata (disptext, phone, bodytext, srt, iconpath) values ('Park 102','102','" + plateNo + "',2," + R.drawable.p102 + ")");
    _db.execSQL("INSERT INTO msgdata (disptext, phone, bodytext, srt, iconpath) values ('Park 103','103','" + plateNo + "',3," + R.drawable.p103 + ")");
    _db.execSQL("INSERT INTO msgdata (disptext, phone, bodytext, srt, iconpath) values ('Park 104','104','" + plateNo + "',4," + R.drawable.p102 + ")");
  }
  /**
   * Called when no data is present as a placeholder for bringing up context menu.
   * @param _db database to populate in
   */
  public void populateTestData(SQLiteDatabase _db) {
    _db.execSQL("INSERT INTO msgdata (disptext, srt, iconpath) values ('Long press for options',0," + R.drawable.icon + ")");
  }
  /**
   * Populates one record in msgdata for paying ticket for Zagreb street tram.
   * @param _db database to populate in
   */
  public void populateZET(SQLiteDatabase _db) {
    _db.execSQL("INSERT INTO msgdata (disptext, phone, bodytext, srt, iconpath) values ('Zet karta','8585','ZG',5," + R.drawable.zet + ")");
  }
  /**
   * Populates info request messages for Vipnet mobile provider. It's for mine and my wife's convenience, I'm not payed for this :) 
   * @param _db
   */
  public void populateVIP(SQLiteDatabase _db) {
    _db.execSQL("INSERT INTO msgdata (disptext, phone, bodytext, srt, iconpath) values ('VIP potrošnja','777','tpo',6," + R.drawable.vip + ")");
    _db.execSQL("INSERT INTO msgdata (disptext, phone, bodytext, srt, iconpath) values ('VIP bespl.promet','777','bsp',7," + R.drawable.vip + ")");
  }
  /**
   * @deprecated This works only for text fields. Use {@link #updateMsgData(int, ContentValues)}
   * @param id
   * @param values
   */
  public void saveMsgdata(int id, String[] values) {
    String updStr = "UPDATE msgdata SET ";
    for (int i = 0; i < values.length; i++) {
      updStr += msgdata_fields[i] + " = '"+values[i]+"', ";
    }
    updStr = updStr.substring(0,updStr.length()-2)+" WHERE _id="+id;
    getWritableDatabase().execSQL(updStr);
  }
  /**
   * Updates msgdata with given {@link ContentValues} cv and having given id
   * @param id id of row to update
   * @param cv values to update
   */
  public void updateMsgData(int id, ContentValues cv) {
    getWritableDatabase().update("msgdata", cv,"_id="+id, null);
  }
  /**
   * Creates the same row as with id with appended '#1' in descritpion text. If '#1' is already appended the number will increase. 
   * @param id
   * @return rowid of new record as return value of {@link getWritableDatabase().insert(String, String, ContentValues) }
   */
  public long duplicateMsgData(int id) {
    Cursor c = getReadableDatabase().query("msgdata", null, "_id="+id, null, null, null, null);
    c.moveToFirst();
    String dispText = c.getString(1);
    if (dispText.contains("#")) {
      int num = 1;
      try {
        StringTokenizer toks = new StringTokenizer(dispText,"#");
        String dt1 = toks.nextToken();
        String dt2 = toks.nextToken();
        num = Integer.parseInt(dt2)+1;
        dispText = dt1;
      } catch (Exception e) {
        // TODO: handle exception
      }
      dispText = dispText + "#" + num;
    } else dispText = dispText + "#1";
    ContentValues cval = new ContentValues();
    for (int i = 0; i < msgdata_fields.length; i++) {
      if (!c.isNull(i+1)) {
        switch (i) {
          case 0:
            cval.put(msgdata_fields[i], dispText);
            break;
          case 3:
            cval.put(msgdata_fields[i], c.getInt(i+1));
            break;
          case 4:
            cval.put(msgdata_fields[i], c.getInt(i+1));
            break;
          default:
            cval.put(msgdata_fields[i], c.getString(i+1));
        }
      }
    }
//    String qry = "INSERT INTO msgdata "+names+values;
//    Log.d(TAG, qry);
    return getWritableDatabase().insert("msgdata", null, cval);
  }
  /**
   * Retrieves icon definition from database. Used when creating shortcut.
   * @param sId id of message data row
   * @param def default definition if row is not found
   * @return value stored in iconpath column in msgdata record with id sId
   */
  public int getMsgIcon(int sId, int def) {
    try {
      Cursor c = getReadableDatabase().query("msgdata", new String[] {"iconpath"}, "_id="+sId,null,null,null,null);
      if (c.getCount() == 0) return def;
      c.moveToFirst();
      if (!c.isNull(0)) return c.getInt(0);
      return def;
    } catch (Exception e) {
      return def;
    }
  }
}
